﻿using System;
using System.Data;
using System.Collections;
using CE.Database;

/// <summary>
/// Summary description for CarDetailTHDB
/// </summary>
namespace CE.CarInsurance
{
    public class CarDetailTHDB
    {
        public Int64 Insert(CarDetailTH cardetail)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCarDetailTHInsertUpdateSingleItem";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@CarId", cardetail.CarId);
            _sPara.Add("@CarMake", cardetail.CarMakeName);
            _sPara.Add("@CarModel", cardetail.CarModelName);
            _sPara.Add("@Body", cardetail.Body);
            _sPara.Add("@CC", cardetail.CC);
            _sPara.Add("@Door", cardetail.Door);
            _sPara.Add("@GroupNo", cardetail.GroupNo);
            _sPara.Add("@MakeModelDesc", cardetail.MakeModelDesc);
            _sPara.Add("@ManufactYear", cardetail.ManufactYear);
            _sPara.Add("@RefCEUserId", cardetail.RefCEUserId);
            _sPara.Add("@CurrentStep", cardetail.CurrentStep);
            _sPara.Add("@TotalStep", cardetail.TotalStep);
            _sPara.Add("@ViewedDetailId", cardetail.ViewedDetailId);
            _sPara.Add("@Status", cardetail.Status);
            _sPara.Add("@Info1", cardetail.Info1);
            _sPara.Add("@Info2", cardetail.Info2);
            _sPara.Add("@Info3", cardetail.Info3);
            _sPara.Add("@Info4", cardetail.Info4);
            _sPara.Add("@Info5", cardetail.Info5);
            _sPara.Add("@Info6", cardetail.Info6);
            _sPara.Add("@Info7", cardetail.Info7);
            _sPara.Add("@Info8", cardetail.Info8);
            _sPara.Add("@Info9", cardetail.Info9);
            _sPara.Add("@Info10", cardetail.Info10);
            _sPara.Add("@Info11", cardetail.Info11);
            _sPara.Add("@Info12", cardetail.Info12);
            _sPara.Add("@Info13", cardetail.Info13);
            _sPara.Add("@Info14", cardetail.Info14);
            _sPara.Add("@Info15", cardetail.Info15);
            _sPara.Add("@Info16", cardetail.Info16);
            _sPara.Add("@Info17", cardetail.Info17);
            _sPara.Add("@Info18", cardetail.Info18);
            _sPara.Add("@Info19", cardetail.Info19);
            _sPara.Add("@Info20", cardetail.Info20);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public bool UpdateCarOther(Int64 CarId, bool IsCarOther)
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = "Update tblCarDetailTH Set BInfo1 = @IsCarOther Where CarId = @CarId;";
            int _result = 0;
            SortedList _sPara = new SortedList();

            _sPara.Add("@CarId", CarId);
            if (IsCarOther)
            {
                _sPara.Add("@IsCarOther", 1);
            }
            else
            {
                _sPara.Add("@IsCarOther", DBNull.Value);
            }

            _result = _db.ExecuteNonSqlStatement(strSQL, _sPara);
            return _result > 0;
        }

        public bool UpdateCarOtherProTrue(Int64 CarId, bool IsCarOther, bool IsTrueYou)
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = "";
            if (IsTrueYou)
            {
                strSQL = "Update tblCarDetailTH Set BInfo1 = @IsCarOther, PromoCode='GG999999', Promotion=4 Where CarId = @CarId;";
            }
            else
            {
                strSQL = "Update tblCarDetailTH Set BInfo1 = @IsCarOther, PromoCode=NULL, Promotion=NULL Where CarId = @CarId;";
            }
            int _result = 0;
            SortedList _sPara = new SortedList();

            _sPara.Add("@CarId", CarId);
            if (IsCarOther)
            {
                _sPara.Add("@IsCarOther", 1);
            }
            else
            {
                _sPara.Add("@IsCarOther", DBNull.Value);
            }

            _result = _db.ExecuteNonSqlStatement(strSQL, _sPara);
            return _result > 0;
        }

        public Int64 GetCarModelCount(int CarMakeId)
        {
            MyDatabase _db = new MyDatabase();
            object _result = null;
            Int64 _retValue = 0;

            _result = _db.ExecuteScalarSqlStatement("Select count(CarMakeId) from tblCarModelInfo Where [Status] = 1 And [CarMakeId] = " + CarMakeId.ToString());
            if (_result != null)
            {
                _retValue = Convert.ToInt64(_result);
            }
            return _retValue;
        }
        
        public DataTable GetCarModel()
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = "SELECT    MA.CarMakeId, MD.CarModelId, MD.CarCode, MA.Description AS CarMake, MD.Description AS Model, MD.Body, MD.CC " +
                            "FROM         tblCarMakeInfo AS MA INNER JOIN " +
                            "tblCarModelInfo AS MD ON MA.CarMakeId = MD.CarMakeId WHERE MD.[Status] = 1 ORDER BY MA.Description, MD.[Description]";

            DataTable _dtCarModel = _db.GetDataTable(strSQL);

            return _dtCarModel;
        }

        public DataTable GetCarModel(int CarMakeId)
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = "SELECT    MA.CarMakeId, MD.CarModelId, MD.CarCode, MA.Description AS CarMake, MD.Description AS Model, MD.Body, MD.CC " +
                            "FROM         tblCarMakeInfo AS MA INNER JOIN " +
                            "tblCarModelInfo AS MD ON MA.CarMakeId = MD.CarMakeId WHERE MD.[Status] = 1 And MA.CarMakeId = " + CarMakeId + " ORDER BY MA.Description, MD.[Description]";

            DataTable _dtCarModel = _db.GetDataTable(strSQL);

            return _dtCarModel;
        }

        public DataTable GetAllCarModel()
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = @"Select MD.CarMakeId, MD.CarModelId, MD.[Description], MD.Body, MD.CC from tblCarModelInfo MD
                            INNER JOIN tblCarMakeInfo MA ON MA.CarMakeId = MD.CarMakeId 
                            Where MA.Status = 1 AND MD.Status = 1  ORDER BY MA.Description, MD.[Description]";

            DataTable _dtCarModel = _db.GetDataTable(strSQL);

            return _dtCarModel;
        }

        public CarDetailTH GetCar(Int64 CarId, Int64 CEUserId)
        {
            CarDetailTH _cardetail = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocCarDetailTHSelectSingleItem";

            SortedList _spara = new SortedList();
            _spara.Add("@CarID", CarId);
            _spara.Add("@RefCEUserId", CEUserId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _cardetail = new CarDetailTH();
                    _cardetail = FillDataRecord(_dt);
                }
            }
            return _cardetail;
        }

        public Int64 UpdateCarDetailPolicy(Int64 CarId, Int64 CEUserId)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCarDetailPolicyInsertUpdateSingleItem";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@CECarId", CarId);
            _sPara.Add("@RefCEUserId", CEUserId);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public bool UpdateLanguage(Int64 CarId, string Language)
        {
            MyDatabase _db = new MyDatabase();
            Int16 IsEnglish = 0;
            if (Language == "en-US")
            {
                IsEnglish = 1;
            }
            string strSQL = "Update tblCarDetailTH Set IsEnglish = @IsEnglish Where CarId = @CarId;";
            int _result = 0;
            SortedList _sPara = new SortedList();

            _sPara.Add("@CarId", CarId);
            _sPara.Add("@IsEnglish", IsEnglish);

            _result = _db.ExecuteNonSqlStatement(strSQL, _sPara);
            return _result > 0;
        }

        private CarDetailTH FillDataRecord(DataTable myDataTable)
        {
            CarDetailTH myCar = new CarDetailTH();
            myCar.CarId = Convert.ToInt64(myDataTable.Rows[0]["CarID"].ToString());
            myCar.CarMakeName = myDataTable.Rows[0]["CarMake"].ToString();
            myCar.CarModelName = myDataTable.Rows[0]["CarModel"].ToString();
            myCar.Body = myDataTable.Rows[0]["Body"].ToString();
            myCar.CC = Convert.ToInt32(myDataTable.Rows[0]["CC"].ToString());
            myCar.Door = Convert.ToInt32(myDataTable.Rows[0]["Door"].ToString());
            myCar.GroupNo = Convert.ToInt32(myDataTable.Rows[0]["GroupNo"].ToString());
            myCar.MakeModelDesc = myDataTable.Rows[0]["MakeModelDesc"].ToString();
            myCar.ManufactYear = Convert.ToInt32(myDataTable.Rows[0]["ManufactYear"].ToString());
            myCar.RefCEUserId = Convert.ToInt64(myDataTable.Rows[0]["RefCEUserId"].ToString());
            myCar.Status = Convert.ToBoolean(myDataTable.Rows[0]["Status"].ToString());
            myCar.Info1 = myDataTable.Rows[0]["Info1"].ToString();
            myCar.Info2 = myDataTable.Rows[0]["Info2"].ToString();
            myCar.Info3 = myDataTable.Rows[0]["Info3"].ToString();
            myCar.Info5 = myDataTable.Rows[0]["Info5"].ToString();
            myCar.Info6 = myDataTable.Rows[0]["Info6"].ToString();
            myCar.Info7 = myDataTable.Rows[0]["Info7"].ToString();
            myCar.Info8 = myDataTable.Rows[0]["Info8"].ToString();
            myCar.Info9 = myDataTable.Rows[0]["Info9"].ToString();
            myCar.Info10 = myDataTable.Rows[0]["Info10"].ToString();
            myCar.Info11 = myDataTable.Rows[0]["Info11"].ToString();
            myCar.Info12 = myDataTable.Rows[0]["Info12"].ToString();
            myCar.Info13 = myDataTable.Rows[0]["Info13"].ToString();
            myCar.Info14 = myDataTable.Rows[0]["Info14"].ToString();
            myCar.Info15 = myDataTable.Rows[0]["Info15"].ToString();
            myCar.Info15 = myDataTable.Rows[0]["Info15"].ToString();

            if (myDataTable.Rows[0]["BInfo1"].ToString() != "")
                myCar.BInfo1 = (bool) myDataTable.Rows[0]["BInfo1"];
            return myCar;
        }

    }
}